﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'proc_cm_ListShotuout')
BEGIN
    PRINT 'Dropping Procedure proc_cm_ListShotuout'
    DROP  Procedure  proc_cm_ListShotuout
END
GO

PRINT 'Creating Procedure proc_cm_ListShotuout'
GO

CREATE PROCEDURE [dbo].[proc_cm_ListShotuout]
	@pHtmlPageId uniqueidentifier
	, @pSectionCd nvarchar(50) = 'Main'
	, @pStartIdx int
	, @pEndIdx int
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @vShoutout TABLE
	(
		idx int identity(1,1) primary key,
		shoutout_id uniqueidentifier not null
	)
	
	INSERT INTO @vShoutout (shoutout_id)
	SELECT
		so.[shoutout_id]
	FROM
		[dbo].[tbl_shoutout] so (nolock)
	WHERE
		so.[html_page_id] = @pHtmlPageId
		and so.[section_cd] = @pSectionCd
	ORDER BY
		so.[create_dttm] DESC

	SELECT
		temp.idx
		,so.[shoutout_id]
		,so.[html_page_id]
		,so.[section_cd]
		,so.[content_txt]
		,u.[UserName] 
		,so.[create_dttm]
		,so.[update_dttm]
		,m.[icon_filename_txt]
	FROM
		@vShoutout temp
		INNER JOIN [dbo].[tbl_shoutout] so (nolock) on temp.shoutout_id = so.shoutout_id
		INNER JOIN [dbo].[aspnet_Users] u (nolock) on so.[UserId] = u.[UserId] 
		INNER JOIN [dbo].[tbl_member] m (nolock) on u.[UserId] = m.[UserId]
	WHERE
		temp.idx BETWEEN @pStartIdx AND @pEndIdx + 1
	ORDER BY
		temp.idx ASC
END
GO

GRANT EXEC ON dbo.proc_cm_ListShotuout TO PUBLIC
GO
